package com.kcs.fxmargin.action;

import java.awt.print.Pageable;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HeaderFooter;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import com.itextpdf.text.Document;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.Rectangle;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfContentByte;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfPageEvent;
import com.itextpdf.text.pdf.PdfTemplate;
import com.itextpdf.text.pdf.PdfWriter;
import com.kcs.common.utils.Constant;
import com.kcs.common.utils.commonUtils;
import com.kcs.fxmargin.action.form.LoginActionForm;
import com.kcs.fxmargin.action.form.OverLimitSummaryReportActionForm;
import com.kcs.fxmargin.bo.OverLimitSummaryReportBO;
import com.kcs.fxmargin.service.OverLimitSummaryReportService;

public class OverLimitSummaryReportAction extends FXAction implements PdfPageEvent{
	
	private Logger log = Logger.getLogger(Constant.LOG_FX);
	private BaseFont baseFont;
	private String userName;
	public PdfTemplate tpl;

	@Override
	public ActionForward process(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		
		OverLimitSummaryReportActionForm bean = null;
		OverLimitSummaryReportBO bo = null;
		OverLimitSummaryReportService svc = null;
		LoginActionForm userInfo = null;
		
		log.info("Begin : OverLimitSummaryReportAction : process");
		
		try{
			userInfo = (LoginActionForm) request.getSession().getAttribute(Constant.LOGIN_DATA_SESSION);
			userName = userInfo.getUserName();
			
			bean = (OverLimitSummaryReportActionForm)form;
			
			log.info(">tranCode : " +bean.getTranCode());

			if(bean.getTranCode().equals("")){
				bean.resetForm();
			
			}else if(bean.getTranCode().equals("searchOverLimitSummary")){
				bo = new OverLimitSummaryReportBO();
				svc = new OverLimitSummaryReportService();
				bo = svc.getFunctionList(bo);
				bean.setOverLimitSummaryList(getOverLimitSummaryListFromBO(bo, request));
				request.setAttribute(Constant.SHOW_RESULT, Constant.SHOW_RESULT);
				
			}else if(bean.getTranCode().equals("exportOverLimitSummary")){
				exportOverLimitSummary(bean.getOverLimitSummaryList(), request, response, userInfo.getUserName());
				request.setAttribute(Constant.SHOW_RESULT, Constant.SHOW_RESULT);
			
			}else if(bean.getTranCode().equals("exportOverLimitSummaryToExcel")){
				exportOverLimitSummaryToExcel(bean.getOverLimitSummaryList(), request, response, userInfo.getUserName());
				request.setAttribute(Constant.SHOW_RESULT, Constant.SHOW_RESULT);
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
			request.setAttribute(Constant.ERR_ATTR, e.getMessage());
			
		}finally{
			if(bean != null){
				bean.setTranCode("");
			}
		}

		log.info("End : OverLimitSummaryReportAction : process");
		return mapping.findForward("overLimitSummaryReport");
	}
	
	public List<OverLimitSummaryReportActionForm> getOverLimitSummaryListFromBO(OverLimitSummaryReportBO bo, HttpServletRequest request)throws Exception{
		List<OverLimitSummaryReportActionForm> resultList = null;
		List<OverLimitSummaryReportBO> boList = null;
		OverLimitSummaryReportActionForm form = null;
		BigDecimal utilization = null;
		BigDecimal b1 = null;
		BigDecimal b2 = null;
		int rowNum = 0;
		String tmp = null;
		
		log.info("Begin : OverLimitSummaryReportAction : getOverLimitSummaryListFromBO");
		
		boList = bo.getOverLimitSummaryList();
		
		if(boList != null){
			resultList = new ArrayList<OverLimitSummaryReportActionForm>();
		}
		for(OverLimitSummaryReportBO boItem : boList){
			form = new OverLimitSummaryReportActionForm();
			form.setAccountName(boItem.getCustName());
			form.setLimitType(boItem.getPreType());
			form.setTotalLimit(commonUtils.decimalFormat(boItem.getCashBal()));
			form.setTotalUsed(commonUtils.decimalFormat(boItem.getTotUsed()));
			form.setAvailable(commonUtils.decimalFormat(boItem.getTotAvail()));
			
			tmp = toStringNotNull(boItem.getTotUsed());
			b1 = new BigDecimal(tmp.equals("")?"0":tmp);
			
			tmp = toStringNotNull(boItem.getCashBal());
			b2 = new BigDecimal(tmp.equals("")?"0":tmp);
			
			try{
				System.out.println(">b1: "+b1);
				System.out.println(">b2: "+b2);
				
				utilization = b1.divide(b2);
				form.setUtilization(commonUtils.decimalFormat(utilization.toString()));
				
				
				System.out.println(">utilization: "+utilization);
				System.out.println(">getUtilization: "+form.getUtilization());
				
			}catch(ArithmeticException ex){
				form.setUtilization("");
			}
			
			form.setRowNum(++rowNum);
			
			resultList.add(form);
		}
		
		
		log.info("End : OverLimitSummaryReportAction : getOverLimitSummaryListFromBO");
		return resultList;
	}
	
	public void exportOverLimitSummary(List<OverLimitSummaryReportActionForm> list, HttpServletRequest request, HttpServletResponse response, String printBy)throws Exception{
		String[] column = new String[]{"No.","Account Name","Limit Type","Total Limit","Total Used","Available","%Utilization"};
		float[] columnWidth = new float[]{30f,340f,70f,100f,100f,100f,90f};
		PdfPTable pdfTable = null;
		PdfPTable pdfHeaderTable = null;
		PdfPCell pdfCell = null;
		Document pdf = null;
		Font font = null;
		String fontFile = null;
		boolean result = false;
		ByteArrayOutputStream baos = null;
		OutputStream os = null;
		Rectangle rect = null;
		PdfWriter writer = null;
		
		log.info("Begin : OverLimitSummaryReportAction : exportOverLimitSummary");
		
		try{
			
			//Get Font Path
			//fontFile = "C:/Windows/Fonts/TAHOMA.ttf";//***
			fontFile = "/app/fxm-jboss/jboss-as/server/fxm-jboss/log/font/ARIALUNI.TTF";
			log.info("fontFile : "+fontFile);
			
			//Create Pdf Document
			log.info("Create Pdf Document");
			pdf = new Document();
			rect = PageSize.A4;
			rect = rect.rotate();
			pdf.setPageSize(rect);
			pdf.setMargins(10f, 10f, 10f, 10f);
			baos = new ByteArrayOutputStream();
			writer = PdfWriter.getInstance(pdf, baos);
            writer.setPageEvent(this);
			pdf.open();
			
			//Add meta data
			log.info("Add meta data");
			pdf.addTitle("Over Limit Summary Report");
			pdf.addSubject("Over Limit Summary Report");
			pdf.newPage();			
			
			//Create Header Table
			log.info("Create Header Table");
			pdfHeaderTable = new PdfPTable(new float[]{100f,100f,100f});
			pdfHeaderTable.setWidthPercentage(95f);
			
			//Header Table row1 col1
			pdfCell = new PdfPCell(new Phrase(""));
			pdfCell.setBorderWidth(0);
			pdfHeaderTable.addCell(pdfCell);
			
			//Header Table row1 col2
			baseFont = BaseFont.createFont(fontFile, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
			font = new Font(baseFont,14,Font.BOLD);
			pdfCell = new PdfPCell(new Phrase("KRUNG THAI BANK PLC.",font));
			pdfCell.setHorizontalAlignment(Element.ALIGN_CENTER);
			pdfCell.setBorderWidth(0);
			pdfHeaderTable.addCell(pdfCell);
			
			//Header Table row1 col3
			pdfCell = new PdfPCell(new Phrase(""));
			pdfCell.setBorderWidth(0);
			pdfHeaderTable.addCell(pdfCell);
			
			//Header Table row2 col1
			pdfCell = new PdfPCell(new Phrase(""));
			pdfCell.setBorderWidth(0);
			pdfHeaderTable.addCell(pdfCell);
			
			//Header Table row2 col2
			baseFont = BaseFont.createFont(fontFile, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
			font = new Font(baseFont,12,Font.BOLD);
			pdfCell = new PdfPCell(new Phrase("Over Limit Summary Report (Corporate)",font));
			pdfCell.setHorizontalAlignment(Element.ALIGN_CENTER);
			pdfCell.setBorderWidth(0);
			pdfHeaderTable.addCell(pdfCell);
	
			//Header Table row2 col3
			pdfCell = new PdfPCell(new Phrase(""));
			pdfCell.setBorderWidth(0);
			pdfHeaderTable.addCell(pdfCell);
			
			pdf.add(pdfHeaderTable);
			pdf.add(new Paragraph(" "));
			
			//Create Table
			log.info("Create Table");
			pdfTable = new PdfPTable(columnWidth);
			
			//Set Table Header Font
			log.info("Set Table Header Font");
			baseFont = BaseFont.createFont(fontFile, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
			font = new Font(baseFont,10,Font.NORMAL);
			
			for(int i=0; i<column.length;i++){
				pdfCell = new PdfPCell(new Phrase(column[i],font));
				pdfCell.setHorizontalAlignment(Element.ALIGN_CENTER);
				pdfTable.addCell(pdfCell);
			}
			
			pdfTable.setWidthPercentage(95f);
			pdfTable.setHeaderRows(1);
			
			//Set Table Data Font
			log.info("Set Table Data Font");
			baseFont = BaseFont.createFont(fontFile, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
			font = new Font(baseFont,8,Font.NORMAL);
			
			//Add Data
			log.info("Add Data : list size = "+(list!=null?list.size():"null"));
			if(list != null){
				for(OverLimitSummaryReportActionForm data : list){
					pdfCell = new PdfPCell(new Phrase(toStringNotNull(data.getRowNum()),font));
					pdfCell.setHorizontalAlignment(Element.ALIGN_CENTER);
					pdfTable.addCell(pdfCell);
					
					pdfCell = new PdfPCell(new Phrase(toStringNotNull(data.getAccountName()),font));
					pdfCell.setHorizontalAlignment(Element.ALIGN_LEFT);
					pdfTable.addCell(pdfCell);
					
					pdfCell = new PdfPCell(new Phrase(toStringNotNull(data.getLimitType()),font));
					pdfCell.setHorizontalAlignment(Element.ALIGN_CENTER);
					pdfTable.addCell(pdfCell);
					
					pdfCell = new PdfPCell(new Phrase(toStringNotNull(data.getTotalLimit()),font));
					pdfCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
					pdfTable.addCell(pdfCell);
					
					pdfCell = new PdfPCell(new Phrase(toStringNotNull(data.getTotalUsed	()),font));
					pdfCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
					pdfTable.addCell(pdfCell);
					
					pdfCell = new PdfPCell(new Phrase(toStringNotNull(data.getAvailable()),font));
					pdfCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
					pdfTable.addCell(pdfCell);
					
					pdfCell = new PdfPCell(new Phrase(toStringNotNull(data.getUtilization()),font));
					pdfCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
					pdfTable.addCell(pdfCell);
				}	
			}
			
			pdf.add(pdfTable);
			pdf.addCreationDate();
			pdf.addCreator(printBy);

			pdf.close();
			
			//Output file
			log.info("Output file");
			response.setHeader("Pragma", "no-cache");
			response.setContentType("application/pdf");
			response.setHeader("Content-Disposition", "attachment; filename="+Constant.PDF_OVER_LIMIT_SUMMARY_FILENAME);
			
			os = response.getOutputStream();
			baos.writeTo(os);
			os.flush();
			os.close();
			
			result = true;
			
		}catch(Exception e){
			e.printStackTrace();
			
			if(pdf != null){
				pdf.close();
			}
			throw e;
		}
		
		log.info("export result: "+result);
		log.info("End : OverLimitSummaryReportAction : exportOverLimitSummary");
	}
	
	public void exportOverLimitSummaryToExcel(List<OverLimitSummaryReportActionForm> list, HttpServletRequest request, HttpServletResponse response, String printBy)throws Exception{
		ServletOutputStream out = null;
		HSSFWorkbook workbook = null;
		HSSFSheet sheet = null;
		HSSFRow row = null;
		HSSFCell cell = null;
		OverLimitSummaryReportActionForm data = null;
		HSSFCellStyle style = null;
		HSSFFont font = null;
		
		log.info("Begin : OverLimitSummaryReportAction : exportOverLimitSummaryToExcel");
		int rowCnt = 0;
		
		if(list != null && list.size()>0){
			workbook = new HSSFWorkbook();
			
			sheet = workbook.createSheet("Export OLS");
			sheet.getPrintSetup().setLandscape(true);
			sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); 

			sheet.setFitToPage(true);
			sheet.getPrintSetup().setScale((short)100);
			//Criteria
			font = workbook.createFont();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			style = workbook.createCellStyle();
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setFont(font);
			style.setWrapText(false);
			
			row = sheet.createRow(rowCnt++);
			cell = row.createCell(0, Cell.CELL_TYPE_STRING);
			cell.setCellValue("KRUNG THAI BANK PLC.");
			cell.setCellStyle(style);
			
			row = sheet.createRow(rowCnt++);
			cell = row.createCell(0, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Over Limit Summary Report (Corporate)");
			cell.setCellStyle(style);
			
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
			sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6));
			
			//-- Header cell style
			font = workbook.createFont();
			style = workbook.createCellStyle();
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setFont(font);
			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			style.setFillForegroundColor(new HSSFColor.AQUA().getIndex());
			
			//style.setFillBackgroundColor(new HSSFColor.AQUA().getIndex());
			
			rowCnt++;
			row = sheet.createRow(rowCnt++);
			cell = row.createCell(0, Cell.CELL_TYPE_STRING);
			cell.setCellValue("No.");
			cell.setCellStyle(style);
			cell = row.createCell(1, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Account Name");
			cell.setCellStyle(style);
			cell = row.createCell(2, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Limit Type");
			cell.setCellStyle(style);
			cell = row.createCell(3, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Total Limit");
			cell.setCellStyle(style);
			cell = row.createCell(4, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Total Used");
			cell.setCellStyle(style);
			cell = row.createCell(5, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Available");
			cell.setCellStyle(style);
			cell = row.createCell(6, Cell.CELL_TYPE_STRING);
			cell.setCellValue("Utilization");
			cell.setCellStyle(style);
			

			for(int i=0; i<list.size(); i++){
				data = list.get(i);
				int colCnt = 0;
				row = sheet.createRow(rowCnt++);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				cell = row.createCell(colCnt++, Cell.CELL_TYPE_STRING);
				cell.setCellValue(i+1);
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
				cell = row.createCell(colCnt++, Cell.CELL_TYPE_STRING);
				cell.setCellValue(data.getAccountName());
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				cell = row.createCell(colCnt++, Cell.CELL_TYPE_STRING);
				cell.setCellValue(data.getLimitType());
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(colCnt++, Cell.CELL_TYPE_STRING);
				cell.setCellValue(data.getTotalLimit());
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(colCnt++, Cell.CELL_TYPE_STRING);
				cell.setCellValue(data.getTotalUsed());
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(colCnt++, Cell.CELL_TYPE_STRING);
				cell.setCellValue(data.getAvailable());
				cell.setCellStyle(style);
				
				style = workbook.createCellStyle();
				style.setWrapText(false);
				style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
				cell = row.createCell(colCnt++, Cell.CELL_TYPE_STRING);
				cell.setCellValue(data.getUtilization());
				cell.setCellStyle(style);
				
			}
			
			java.util.Iterator<HSSFCell> it = row.cellIterator(); 
		    while(it.hasNext()){ 
			         HSSFCell cellTmp = it.next(); 
			         sheet.autoSizeColumn((short)cellTmp.getCellNum(),true); 
			 }

//			workbook.setPrintArea(0, 0, 13, 0, rowCnt-1);
			
			HSSFFooter footer = sheet.getFooter();
			footer.setLeft("Print By : "+printBy);
			footer.setRight("Print Date : "+commonUtils.getcurrentDatetime());
			
			response.setHeader("Pragma", "no-cache");
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename="+Constant.EXCEL_OVER_LIMIT_SUMMARY_FILENAME);
						
			out = response.getOutputStream();
			workbook.write(out);
			out.flush();
			out.close();
			
			log.info("Export excel successfully.");
			
		}else{
			throw new Exception("Cannot export Excel: Data error.");
		}
		
		log.info("Begin : OverLimitSummaryReportAction : exportOverLimitSummaryToExcel");
		 
	}
	
	private String toStringNotNull(Object o)throws Exception{
		return (o==null)?"":o.toString();
	}

	public void onChapter(PdfWriter arg0, Document arg1, float arg2,
			Paragraph arg3) {
		// TODO Auto-generated method stub
		
	}

	public void onChapterEnd(PdfWriter arg0, Document arg1, float arg2) {
		// TODO Auto-generated method stub
		
	}

	public void onCloseDocument(PdfWriter arg0, Document arg1) {
		// TODO Auto-generated method stub
		
	}

	public void onEndPage(PdfWriter writer, Document document) {
		PdfContentByte cb = writer.getDirectContent();
        cb.saveState();

        float textBase = document.bottom() - 5;
        cb.beginText();
        cb.setFontAndSize(baseFont, 8);

        String text = "Print by : "+userName;
        cb.setTextMatrix(document.left(), textBase);
        cb.showText(text);

        text = "Print date : "+commonUtils.getcurrentDatetime();
        float adjust = baseFont.getWidthPoint("0", 8);
        float textSize = baseFont.getWidthPoint(text, 8);
        cb.setTextMatrix(document.right() - textSize - adjust, textBase);
        cb.showText(text);

        cb.endText();
        cb.addTemplate(tpl, document.right() - adjust, textBase);
        cb.restoreState();
	}

	public void onGenericTag(PdfWriter arg0, Document arg1, Rectangle arg2,
			String arg3) {
		// TODO Auto-generated method stub
		
	}

	public void onOpenDocument(PdfWriter writer, Document document) {
		try {
            // initialization of the template
            tpl = writer.getDirectContent().createTemplate(100, 100);
            tpl.setBoundingBox(new Rectangle(-20, -20, 100, 100));
        } catch(Exception e) {
            System.out.println("ReportGSB709Generater.onOpenDocument() : " + e.toString());
        }
	}

	public void onParagraph(PdfWriter arg0, Document arg1, float arg2) {
		// TODO Auto-generated method stub
		
	}

	public void onParagraphEnd(PdfWriter arg0, Document arg1, float arg2) {
		// TODO Auto-generated method stub
		
	}

	public void onSection(PdfWriter arg0, Document arg1, float arg2, int arg3,
			Paragraph arg4) {
		// TODO Auto-generated method stub
		
	}

	public void onSectionEnd(PdfWriter arg0, Document arg1, float arg2) {
		// TODO Auto-generated method stub
		
	}

	public void onStartPage(PdfWriter arg0, Document arg1) {
		// TODO Auto-generated method stub
		
	}

}
